﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_GetLastRestoredFile]
#-- Purpose:		Returns a resultset containing information about the last
#--					file restored.  Will always be only a single row.
#--	Last Update:	05/23/2013
#--					For a complete history - please review comments in SVN
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_GetLastRestoredFile]
(
	@database_name			sysname
)
AS

SET NOCOUNT ON

SELECT		TOP 1
			CAST(CASE WHEN NOT (rh.stop_at IS NULL AND rh.stop_at_mark_name IS NULL AND rh.stop_before IS NULL) THEN 0 ELSE 1 END as bit) is_full_restore,
			bs.machine_name,
			bs.database_name,
			bmf.physical_device_name,
			rh.restore_date,
			CAST((SELECT state FROM sys.databases (NOLOCK) WHERE name = @database_name) as tinyint) destination_state,
			CAST((SELECT is_in_standby FROM sys.databases (NOLOCK) WHERE name = @database_name) as bit) destination_is_standby
FROM		msdb.dbo.restorehistory rh
JOIN		msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
JOIN		msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE		rh.destination_database_name = @database_name
			AND rh.restore_date = (	SELECT	MAX(restore_date) 
									FROM	msdb.dbo.restorehistory 
									WHERE	destination_database_name = @database_name)
ORDER BY	rh.restore_date DESC

SET NOCOUNT OFF
